USE master
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'ManagementSystemStudent')
DROP DATABASE ManagementSystemStudent
GO

create database ManagementSystemStudent
go
use ManagementSystemStudent
go
--select * from [User]

go
create table [Role](
	roleID varchar(10) primary key,
	roleName nvarchar(20)
)
go
create table UserInfomation(
	UserID varchar(100) primary key,
	[Password] varchar(100) NOT NULL,
	Firstname nvarchar(100)NOT NULL,
	Lastname nvarchar(100)NOT NULL,
	Birthdate smalldatetime NOT NULL,
	[Address] nvarchar(100),
	Phone varchar(100),
	Email nvarchar(100)
)
go
<<<<<<< .mine
create table [Role](
	RoleID varchar(100) primary key,
	RoleName nvarchar(100) NOT NULL
=======
create table Semester(
	semesterID varchar(10) primary key,
	semesterName nvarchar(30),
	[description] nvarchar(100)
>>>>>>> .r12
)
go
<<<<<<< .mine
create table Role_User(	
	RoleID varchar(100) foreign key references [Role](RoleID),
	UserID varchar(100) foreign key references [User](UserID)
)
go
=======
>>>>>>> .r12
<<<<<<< .mine

create table [Subject](
	SubjectID varchar(100) primary key,
	SubjectName nvarchar(100) NOT NULL
=======
create table Course(
	courseID varchar(10) primary key,
	courseName nvarchar(20),
	semesterID varchar(10) foreign key references Semester(semesterID),
	[description] nvarchar(200)
>>>>>>> .r12
)
go
<<<<<<< .mine
create table Mark(
	UserID varchar(100)  references [User](UserID),
	SubjectID varchar(100) references [Subject](SubjectID),
	MarkW float NOT NULL DEFAULT 0,
	MarkP float NOT NULL DEFAULT 0,
	constraint pk_MarkID primary key(UserID,SubjectID)
=======
create table [User](
	userID varchar(10) references UserInfomation(userID),
	roleID varchar(10) references [Role](roleID),
	courseID varchar(10) primary key references Course(courseID)
>>>>>>> .r12
)
go
<<<<<<< .mine
CREATE TABLE Attendance_Detail
(	
	UserID varchar(100) FOREIGN KEY REFERENCES [User](UserID),
	SubjectID varchar(100) FOREIGN KEY REFERENCES [Subject](SubjectID),
	[Status] varchar(100) NOT NULL CHECK([Status] = 'attend' OR [Status] = 'late' OR [Status] = 'absent'),	 
	DateAttendance smalldatetime DEFAULT GETDATE()
=======


create table [Subject](
	subjectID varchar(10) primary key,
	courseID varchar(10) foreign key references [User](courseID),
	subjectName nvarchar(50),
	[description] nvarchar(200),
	numberOfLessons varchar(10)
>>>>>>> .r12
)
<<<<<<< .mine
GO

SELECT *FROM Attendance_Detail

CREATE TABLE Test
(
	[status] varchar(100) REFERENCES [User](UserID),
	val int
=======
go
create table Mark(
	userID varchar(10)  references UserInfomation(userID),
	subjectID varchar(10) references [Subject](subjectID),
	markW varchar(3),
	markP varchar(3),
	constraint pk_MarkID primary key(userID,subjectID)
>>>>>>> .r12
)
GO
INSERT INTO Test Values('admin',1)

SELECT * FROM Test
INSERT INTO [User](UserID, [Password]) VALUES ('admin','123456')

create table TimeTable(
	timetableID varchar(10) primary key,
	subjectID varchar(10) foreign key references [Subject](subjectID),
	startTime nvarchar(100),
	endTime nvarchar(100),
	[date] nvarchar(100)
)
go
create table Attendance(
<<<<<<< .mine
	UserID varchar(100) references [User](UserID),
	[Date] smalldatetime,
	[Status] nvarchar(100)	
=======
	userID varchar(10) references UserInfomation(userID),
	timetableID varchar(10) references TimeTable(timetableID),
	[status] nvarchar(20),
	constraint pk_UserID_timetableID primary key(userID,timetableID)
>>>>>>> .r12
)
GO

<<<<<<< .mine
create table Course(
	CourseID varchar(100) primary key,
	CourseName nvarchar(100) NOT NULL,
	StartCourse nvarchar(100),
	EndCourse nvarchar(100),
)
=======
insert into UserInfomation(userID,[password]) values('admin','123456')
>>>>>>> .r12
go
create table Course_User(
	CourseID varchar(100) foreign key references Course(courseID),
	UserID varchar(100) foreign key references [User](userID),
	Course_user_id varchar(100) primary key
)
go

insert into [User](userID,[password],Firstname,Lastname,Birthdate) values('admin','123456','1','1',GETDATE())
go

create proc sp_GetAllUser
as
begin
	select userID,[password] 
	from UserInfomation
end
go
